# coding=utf8
import sys

import math
import threading

import pymysql
from pymongo import MongoClient

import json
import datetime

from mongoengine.base import BaseDocument


class MongoHolder():
    mongodb = None

    def initMongoDB(self, uri, port, dbname):
        client = MongoClient(uri, port, maxPoolSize=200, connectTimeoutMS=60 * 1000, socketTimeoutMS=60 * 1000)
        self.mongodb = client[dbname]

    def readMongoTable(self, page, limit,collection):
        return self.mongodb[collection].find().skip(page * limit).limit(limit)

    def countMongoDB(self,collection):
        return self.mongodb[collection].estimated_document_count()

    def getMongoTableList(self):
        return  self.mongodb.list_collection_names()



# 使json能够转化datetime对象
class DateEncoder(json.JSONEncoder):

    def default(self, obj):
        if isinstance(obj, datetime.datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S.%f')
        elif isinstance(obj, datetime.date):
            return obj.strftime("%Y-%m-%d")
        else:
            return json.JSONEncoder.default(self, obj)




class MongoToJson():

    def convert(self,dic_data):
        # 对于引用的Id和该条数据的Id，这里都是ObjectId类型的
        from bson import ObjectId
        try:
            # 字典遍历
            for key, value in dic_data.items():
                # 如果是列表，则递归将值清洗
                if isinstance(value, list):
                    for l in value:
                        self.convert(l)

                elif isinstance(value, dict):
                    self.convert(value)

                else:
                    if isinstance(value, ObjectId):
                        dic_data[key] = str(dic_data.pop(key))
        except Exception as e:
            pass

        return dic_data

    # 将 MongoDB 的 document转化为json形式
    def convertMongoToJson(self,o):
        ret = {}
        # 判断其是否为Document
        if isinstance(o, BaseDocument):
            data = o.to_mongo()
            # 转化为字典
            data = data.to_dict()
            ret =self.convert(data)
        else:
            ret =self.convert(o)
        # 将数据转化为json格式， 因json不能直接处理datetime类型的数据，故需要区分处理
        ret = json.dumps(ret, cls=DateEncoder)
        return ret


class MySQLHolder():
    mysql = None
    mysql_db = None  # mySQL数据库名称

    def initMySql(self, host, port, user, passwd, dbname):
        self.mysql = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=passwd,
            database=dbname,
            charset="utf8"
        )

    def hasMySqlTableForDB(self, tableName):
        base_sql = "SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME='%s'"
        cursor = self.mysql.cursor()
        cursor.execute(base_sql % (self.mysql_db, tableName))
        data = cursor.fetchone()
        return data[0] > 0

    def createMySqlTable(self, tableName):
        try:
            cursor = self.mysql.cursor()
            if self.hasMySqlTableForDB(tableName):
                base_sql='drop table `%s`'
                cursor.execute(base_sql % tableName)

            base_sql = 'CREATE TABLE `%s` (`id` INT NOT NULL AUTO_INCREMENT,`datajson` json,PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8'
            cursor.execute(base_sql % tableName)
            data = cursor.fetchone()
            return data
        except Exception as e:
            print(e)
            self.mysql.rollback()
            pass
        finally:
            self.mysql.commit()


    def executeInsertSQL(self,db,tableName,mysql_data):
        json=mysql_data.replace("'","\\'").replace(r'\"',r'\\"').replace(r'\\n',r'\n').replace(r'\\t',r'\t').replace(r'\\r',r'\r').replace(r'\n',r'\\n').replace(r'\t',r'\\t').replace(r'\r',r'\\r')
        sql = "INSERT INTO %s (datajson) VALUES('%s')" % (tableName,json)
        try:
            cursor = db.cursor()
            cursor.execute(sql)
        except Exception as e:
            print("json={},sql={}".format(mysql_data,sql))
            print(e)
            db.rollback()




if __name__ == '__main__':
    mongodb_uri = "mongodb://10.206.36.25"
    mongodb_port = 27017
    mongodb_db = "gesb_config"
    mongodb_collection = None

    mysql_host = "10.206.36.39"
    mysql_port = 3306
    mysql_user = "eds"
    mysql_passwd = "Ymdd@666"
    mysql_db = "gesb_config"
    mysql_table = None

    mongo_conn = MongoHolder()
    mongo_conn.initMongoDB(mongodb_uri, mongodb_port, mongodb_db)

    mysql_conn = MySQLHolder()
    mysql_conn.mysql_db = mysql_db
    mysql_conn.initMySql(mysql_host, mysql_port, mysql_user, mysql_passwd, mysql_db)

    mongoToJson=MongoToJson()


    #   开始同步数据
    mongodb_collection_list=[]

    if mongodb_collection!=None and mongodb_collection!="":
        mongodb_collection_list.append(mongodb_collection)
    else:
        mongodb_collection_list= mongo_conn.getMongoTableList()


    collectionCount=len(mongodb_collection_list)

    for i in range(0,collectionCount):

        mongodb_collection=mongodb_collection_list[i]

        #         if mongodb_collection in ['mqMes','timerLogInfo','serialNum','flowProcessTimes4h','mQTimes4h']:
        #             continue

        mysql_table=mongodb_collection

        limit = 100
        document_count = mongo_conn.countMongoDB(mongodb_collection)
        document_all_page = int(math.ceil(document_count / limit))


        print("[开始]将collection={},同步到table={}, 表执行进度={}/{}，document的数量={}".format(mongodb_collection,mysql_table,i+1,collectionCount,document_count))

        mysql_conn.createMySqlTable(mysql_table)


        for page in range(0, document_all_page):

            print("collection={},当前document的执行进度{}/{}页,每页100条".format(mongodb_collection,page+1,document_all_page))

            mongo_data_list = mongo_conn.readMongoTable(page, limit,mongodb_collection)

            mysql_data_list=[]

            for mongo_data in mongo_data_list:

                try:
                    mysql_data=mongoToJson.convertMongoToJson(mongo_data)

                    mysql_conn.executeInsertSQL(mysql_conn.mysql,mysql_table,mysql_data)

                except Exception as e:
                    print("do={},mysql_data={},type={}".format(e,mysql_data,type(mysql_data)))

            mysql_conn.mysql.commit()

        #   threading.Thread(target=disposeData, args=(data,mysql_conn)).start()

        print("[结束]将collection={},同步到table={}, 表执行进度={}/{}，document的数量={}".format(mongodb_collection,mysql_table,i+1,collectionCount,document_count))
    mysql_conn.mysql.close()
